package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * @author Pavel
 */

public class DBProduct implements IFDBProduct
{
    private Connection db_con;

    public DBProduct()
    {
        db_con = DBConnection1.getInstance().getDBcon();
    }
    
    private Product buildProduct(ResultSet results)
    {
        Product prodObj = new Product();
        
        try
        {
            prodObj.setId(results.getInt(1));
            prodObj.setName(results.getString(2));
            prodObj.setPurchasePrice(results.getFloat(3));
            prodObj.setSalesPrice(results.getFloat(4));
            prodObj.setRentPrice(results.getFloat(5));
            prodObj.setCountry(results.getString(6));
            prodObj.setMinStock(results.getInt(7));
            prodObj.setSupplier(new Supplier(results.getInt(8)));
            System.out.println("Build product" + prodObj.getName());
        }
        
        catch (Exception e)
        {
            System.out.println("error in building the product object");
        }
        
        return prodObj;
    }
    
    private String buildQuery(String wClause)
    {
        String query = "SELECT * FROM Product";
        
        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        
        return query;
    }
    
    //use singleWhere to select only one product at a time
    private Product singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        Product prodObj = new Product();
        
        String query = buildQuery(wClause);
        System.out.println(query);
        
        try
        {// read the product from the database
        	Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            if (results.next())
            {
                prodObj = buildProduct(results);
                stmt.close();
            }
            if(retrieveAssociation)
            {//the supplier is to be built as well
            	IFDBSupplier dbSupplier=new DBSupplier();
            	Supplier suppObj=dbSupplier.getSupplierByName(prodObj.getSupplier().getName(), false);
            	System.out.println("Supplier is selected.");
            	prodObj.setSupplier(suppObj); //supplier is selected
            }
            else
            {
            	prodObj=null;
            }
        }
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return prodObj;
    }
    
    //use miscWhere to select multiple products at a time
    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Product> prodList = new ArrayList<Product>();

        String query = buildQuery(wClause);

        try 
        {
        	Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            while (results.next())
            {
                Product prodObj = new Product();
                prodObj = buildProduct(results);
                prodList.add(prodObj);
            }
            stmt.close();
            if(retrieveAssociation)
            {//the supplier is to be built as well
            	IFDBSupplier dbSupp=new DBSupplier();
            	for(Product prodObj : prodList)
            	{
            		int SuppID=prodObj.getSupplier().getId();
            		Supplier suppObj=dbSupp.getSuppById(SuppID, false);
            		prodObj.setSupplier(suppObj);
            		System.out.println("Supplier is selected.");
                }
            }
        }
        catch (Exception e)
        {
        	System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return prodList;
    }
    
    public ArrayList<Product> getProductsBySuppID(int suppId, boolean retrieveAssociation)
    {
    	return miscWhere(" suppID= " + suppId, retrieveAssociation);
    }
    
    @Override
	//get all products
	public ArrayList<Product> getAllProducts(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	@Override
	//get a product by its id
	public Product getProductByID(int id, boolean retrieveAssociation)
	{
		String wClause = " ProdID= '" + id + "'";
        return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	//get a product by its name
	public Product getProductByName(String name, boolean retrieveAssociation)
	{
		String wClause=" Name = '" + name + "'";
		return singleWhere(wClause, retrieveAssociation);
	}
	
	//insert a product into the database
    public int insertProduct(Product prodObj) throws Exception 
    {
        int result = -1;
        String query = "INSERT INTO Product(Name, PurchasePrice, SalesPrice, RentPrice, CoutntryOfOrigin, MinStock, SuppID) VALUES ('" + 
        //prodObj.getId() + "', '" +
        prodObj.getName() + "', '" +
        prodObj.getPurchasePrice() + "', '" +
        prodObj.getSalesPrice() + "', '" +
        prodObj.getRentPrice() + "', '" +
        prodObj.getCountry() + "', '" +
        prodObj.getMinStock() + "')";
        
        System.out.println("insert: " + query);
        
        try
        {
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }
        
        catch (SQLException ex)
        {
            System.out.println("Product not created.");
            throw new Exception("Product has not been inserted correctly.");
        }
        return result;
    }
    
    @Override
	//update a product by its id
	public int updateProductByID(Product prod)
	{
		Product prodObj=prod;
		
		int result=-1;
		
		String query= "UPDATE Product SET " +
		"Name = '" + prodObj.getName() + "' " +
		"PurchasePrice= '" + prodObj.getPurchasePrice() + "' " +
		"SalesPrice= '" + prodObj.getSalesPrice() + "' " +
		"RentPrice= '" + prodObj.getRentPrice() + "' " +
		"CountryOfOrigin= '" + prodObj.getCountry() + "' " +
		"MinStock= '" + prodObj.getMinStock() + "' " +
		"SuppID= '" + prodObj.getSupplier().getId() + "' " +
		"WHERE ProdID= '" + prodObj.getId() + "'";
		System.out.println("Update query:" + query);
		
		try
		{
			Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
		
		catch(Exception ex)
		{
	 	 	System.out.println("Update exception in Product db: "+ex);
	  	}
		return(result);
	}

	@Override
	//update a product by its name
	public int updateProductByName(Product prod) 
	{
		Product prodObj=prod;
		
		int result=-1;
		
		String query= "UPDATE Product SET " +
		"Name = '" + prodObj.getName() + "' " +
		"PurchasePrice= '" + prodObj.getPurchasePrice() + "' " +
		"SalesPrice= '" + prodObj.getSalesPrice() + "' " +
		"RentPrice= '" + prodObj.getRentPrice() + "' " +
		"CountryOfOrigin= '" + prodObj.getCountry() + "' " +
		"MinStock= '" + prodObj.getMinStock() + "' " +
		"SuppID= '" + prodObj.getSupplier().getId() + "' " +
		"WHERE Name= '" + prodObj.getName() + "'";
		System.out.println("Update query:" + query);
		
		try
		{
			Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
		
		catch(Exception ex)
		{
	 	 	System.out.println("Update exception in Product db: "+ex);
	  	}
		return(result);
	}

	@Override
	public int deleteProductByID(int id)
	{
		int result = -1;
    	String query = "DELETE FROM Products WHERE ProdID= '" + id + "'";
        System.out.println(query);
        try
        {//delete from product a product
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception ex)
        {
            System.out.println("Delete exception in Product db: " + ex);
        }
        return result;
	}

	@Override
	public int deleteProductByName(String name)
	{
		int result = -1;
    	String query = "DELETE FROM Products WHERE Name= '" + name + "'";
        System.out.println(query);
        try
        {//delete from product a product
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception ex)
        {
            System.out.println("Delete exception in Product db: " + ex);
        }
        return result;
	}
}